import sys
import re
import sqlite3

from PyQt5.QtSql import QSqlDatabase, QSqlQuery
from PyQt5.QtWidgets import *
from PyQt5 import QtCore


class MyWindow(QMainWindow):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.setWindowTitle("Поиск аттестата")
        self.resize(1280, 600)

        self.surname = ""
        self.first_name = ""
        self.patronymic = ""

        self.certificate_series = ""
        self.certificate_number = ""

        self.year = ""
        self.month = ""
        self.day = ""

        self.year_of_birth = ""
        self.month_of_birth = ""
        self.day_of_birth = ""
        
        con = sqlite3.connect("зсш.db")
        self.cur = con.cursor()
        
        self.layout = QHBoxLayout()
        
        self.table = QTableWidget()
        self.table.setColumnCount(7)
        self.table.setHorizontalHeaderLabels(
            ["Фамилия", "Имя", "Отчество",
             "Серия аттестата", "Номер аттестата",
             "Дата выдачи", "Дата рождения"]
        )

        self.table.cellClicked.connect(self.table_cell_clicked)

        self.layout.addWidget(self.table)

        self.options_layout = QVBoxLayout()
        self.layout.addLayout(self.options_layout)
        
        self.name_layout = QHBoxLayout()
        self.options_layout.addLayout(self.name_layout)
        # self.name_layout.addStretch()
        
        self.surname_line = QLineEdit()
        self.first_name_line = QLineEdit()
        self.patronymic_line = QLineEdit()

        self.surname_line.setPlaceholderText("Фамилия")
        self.first_name_line.setPlaceholderText("Имя")
        self.patronymic_line.setPlaceholderText("Отчество")

        self.surname_line.textChanged.connect(
            lambda surname: setattr(self, "surname", surname)
        )
        self.surname_line.textChanged.connect(self.display_query)

        self.first_name_line.textChanged.connect(
            lambda first_name: setattr(self, "first_name", first_name)
        )
        self.first_name_line.textChanged.connect(self.display_query)
        
        self.patronymic_line.textChanged.connect(
            lambda patronymic: setattr(self, "patronymic", patronymic)
        )
        self.patronymic_line.textChanged.connect(self.display_query)

        self.name_layout.addWidget(self.surname_line)
        self.name_layout.addWidget(self.first_name_line)
        self.name_layout.addWidget(self.patronymic_line)

        self.certificate_layout = QHBoxLayout()
        self.options_layout.addLayout(self.certificate_layout)
        # self.certificate_layout.addStretch()

        self.certificate_series_line = QLineEdit()
        self.certificate_number_line = QLineEdit()

        self.certificate_series_line.setPlaceholderText("Серия аттестата")
        self.certificate_number_line.setPlaceholderText("Номер аттестата")

        self.certificate_series_line.textChanged.connect(
            lambda series: setattr(self, "certificate_series", series)
        )
        self.certificate_series_line.textChanged.connect(self.display_query)
        
        self.certificate_number_line.textChanged.connect(
            lambda number: setattr(self, "certificate_number", number)
        )
        self.certificate_number_line.textChanged.connect(self.display_query)

        self.certificate_layout.addWidget(self.certificate_series_line)
        self.certificate_layout.addWidget(self.certificate_number_line)

        self.date_layout = QHBoxLayout()
        self.options_layout.addLayout(self.date_layout)
        # self.date_layout.addStretch()

        self.year_line = QLineEdit()
        self.month_line = QLineEdit()
        self.day_line = QLineEdit()

        self.year_line.setPlaceholderText("Год выдачи")
        self.month_line.setPlaceholderText("Месяц выдачи (цифрами)")
        self.day_line.setPlaceholderText("День выдачи")

        self.year_line.textChanged.connect(
            lambda year: setattr(self, "year", year)
        )
        self.year_line.textChanged.connect(self.display_query)

        self.month_line.textChanged.connect(
            lambda month: setattr(self, "month", month)
        )
        self.month_line.textChanged.connect(self.display_query)
        
        self.day_line.textChanged.connect(
            lambda day: setattr(self, "day", day)
        )
        self.day_line.textChanged.connect(self.display_query)

        self.date_layout.addWidget(self.year_line)
        self.date_layout.addWidget(self.month_line)
        self.date_layout.addWidget(self.day_line)
        
        self.birthdate_layout = QHBoxLayout()
        self.options_layout.addLayout(self.birthdate_layout)
        # self.birthdate_layout.addStretch()

        self.year_of_birth_line = QLineEdit()
        self.month_of_birth_line = QLineEdit()
        self.day_of_birth_line = QLineEdit()

        self.year_of_birth_line.setPlaceholderText("Год рождения")
        self.month_of_birth_line.setPlaceholderText("Месяц рожднения (цифрами)")
        self.day_of_birth_line.setPlaceholderText("День рождения")

        self.year_of_birth_line.textChanged.connect(
            lambda year_of_birth: setattr(self, "year_of_birth", year_of_birth)
        )
        self.year_of_birth_line.textChanged.connect(self.display_query)

        self.month_of_birth_line.textChanged.connect(
            lambda month_of_birth: setattr(self, "month_of_birth", month_of_birth)
        )
        self.month_of_birth_line.textChanged.connect(self.display_query)

        self.day_of_birth_line.textChanged.connect(
            lambda day_of_birth: setattr(self, "day_of_birth", day_of_birth)
        )
        self.day_of_birth_line.textChanged.connect(self.display_query)

        self.birthdate_layout.addWidget(self.year_of_birth_line)
        self.birthdate_layout.addWidget(self.month_of_birth_line)
        self.birthdate_layout.addWidget(self.day_of_birth_line)

        self.secondary_table = QTableWidget(7, 1)

        self.secondary_table.setHorizontalHeaderLabels(
            ["Значение"]
        )

        self.secondary_table.setVerticalHeaderLabels(
            ["Фамилия", "Имя", "Отчество",
             "Серия аттестата", "Номер аттестата",
             "Дата выдачи", "Дата рождения"]
        )

        self.options_layout.addWidget(self.secondary_table)

        query = self.get_default_query()
        
        self.display_query()
        self.table_cell_clicked(0, 0)

        central_widget = QWidget()
        central_widget.setLayout(self.layout)

        self.setCentralWidget(central_widget)

    def display_query(self, query: list[tuple] = None, table: QTableWidget = None):
        if (query is None or isinstance(query, str)) and table is None:
            query = self.get_default_query()
            table = self.table

        table.setRowCount(len(query))

        for record_index, record in enumerate(query):
            for item_index, item in enumerate(record):
                table_item = QTableWidgetItem(
                    re.sub("\d*:\d*\d*", "", str(item))
                )

                table_item.setFlags(QtCore.Qt.ItemIsEnabled)

                table.setItem(record_index, item_index, table_item)

        table.resizeColumnsToContents()

    def get_query(self,
            surname, first_name, patronymic,
            certificate_series, certificate_number,
            year, month, day,
            year_of_birth, month_of_birth, day_of_birth
            ) -> list[tuple]:
        return self.cur.execute(
                    f"""
                    SELECT "Фамилия получателя", "Имя получателя", 
                           "Отчество получателя", "Серия документа",
                           "Номер документа", "Дата выдачи",
                           "Дата рождения получателя"
                    FROM main

                    WHERE "Фамилия получателя" LIKE '{surname.capitalize()}%'
                    AND "Имя получателя" LIKE '{first_name.capitalize()}%'
                    AND "Отчество получателя" LIKE '{patronymic.capitalize()}%'

                    AND "Серия документа" LIKE '{certificate_series.upper()}%'
                    AND "Номер документа" LIKE '{certificate_number}%'
                    
                    AND "Дата выдачи" LIKE '{year}%-{month}%-{day}%'

                    AND "Дата рождения получателя" LIKE
                        '{year_of_birth}%-{month_of_birth}%-{day_of_birth}%'
                    """
                ).fetchall()

    def get_default_query(self):
        return self.get_query(
                self.surname, self.first_name, self.patronymic,
                self.certificate_series, self.certificate_number,
                self.year, self.month, self.day,
                self.year_of_birth, self.month_of_birth,
                self.day_of_birth
        )

    def display_record_from_tablese(
        self, row_index: int, from_table: QTableWidget, to_table: QTableWidget
    ) -> None:
        """Display record from from_table to to_table
        """

        for column_index in range(from_table.columnCount()):
            item = from_table.item(row_index, column_index)
            to_table.setItem(column_index - 1, 1, item.clone())

    def table_cell_clicked(self, row: int, column: int) -> None:
        self.display_record_from_tablese(row, self.table, self.secondary_table)


if __name__ == '__main__':
    app = QApplication(sys.argv)
    window = MyWindow()
    window.show()
    sys.exit(app.exec_())
